import xlrd
import json
import os
from tkinter import *
from tkinter.filedialog import askdirectory
from tkinter.ttk import *
from tkinter import messagebox


def convent_excel(path, name):
    excel_name = os.path.splitext(name)[0]
    data = xlrd.open_workbook(os.path.join(path, name))
    for sheet_name in data.sheet_names():
        table = data.sheet_by_name(sheet_name)
        nrows = table.nrows  # 获取该sheet中的有效行数
        if nrows == 0:
            break
        objs = []
        for rowx in range(table.nrows):
            if rowx == 0:
                continue
            keys = table.row_values(0)
            values = table.row_values(rowx)
            dic = {}
            for i in range(len(keys)):
                dic[keys[i]] = values[i]
            objs.append(dic)
        with open(os.path.join(path, excel_name + '.json'), 'w', encoding='utf-8') as f:
            json.dump(objs, f, ensure_ascii=False, indent=4)
        showLog(name + '完成')


def scanningdir():
    for file in os.listdir(dir_path.get()):
        if file.endswith('.xls') or file.endswith('.xlsx'):
            convent_excel(dir_path.get(), file)


def showLog(text):
    textBox.insert('end', text)  # 显示在text框里面
    textBox.insert('insert', '\n')  # 换行
    textBox.see("end")
    textBox.update()  # 刷新


if __name__ == '__main__':
    root = Tk()
    root.title("Excel转Json工具")
    dir_path = StringVar()  # 源文件目录

    # 目录选择
    Label(root, text="Excel目录:").grid(row=0, column=0)
    Entry(root, textvariable=dir_path).grid(row=0, column=1)
    Button(root, text="选择目录", command=lambda: dir_path.set(askdirectory())).grid(row=0, column=2)
    # 执行按钮
    beginBtn = Button(root, text="启动", command=scanningdir)
    beginBtn.grid(row=1, column=2)

    textBox = Text(root)
    textBox.grid(row=2, column=0, columnspan=3, sticky=W + E + N + S, padx=5, pady=5)

    root.mainloop()
